﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LanguageInfo.Entity;
using System.Data.SqlClient;
using LanguageInfo.Data.Helper;
using System.Data;
using LanguageInfo.IDAL;
using System.Globalization;
using System.Collections;
using System.Data.Common;
using System.Text.RegularExpressions;

namespace LanguageInfo.DAL
{
    public class UserInfoDAL : DataBaseClass, IUserInfoDAL
    {
        protected override void LoadRow<T>(IDataReader reader, ref T item)
        {
            UserInfo userInfo = (UserInfo)Convert.ChangeType(item, typeof(T), CultureInfo.InvariantCulture);
            userInfo.Account = reader["Account"].ToString();
            userInfo.Address = reader["Address"].ToString();
            userInfo.Company = reader["Company"].ToString();
            userInfo.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
            userInfo.Email = reader["Email"].ToString();
            userInfo.LoginTime = Convert.ToDateTime(reader["LoginTime"]);
            userInfo.Phone = reader["Phone"].ToString();
            userInfo.Role = Convert.ToInt32(reader["Role"]);
            userInfo.Sex =  Convert.ToInt32(reader["Sex"]);
            userInfo.Type = Convert.ToInt32(reader["Type"]);
            userInfo.UserName = reader["UserName"].ToString();
            userInfo.ZipCode = reader["ZipCode"].ToString();
            userInfo.Password = reader["Password"].ToString();
            //if (!(reader["BrowseTime"] is DBNull))
            //{
            //    narInfoallView.BrowseTime = Convert.ToDateTime(reader["BrowseTime"]);
            //}
            //if (!(reader["IsEdit"] is DBNull))
            //{
            //    narInfoallView.IsEdit = Convert.ToInt32(reader["IsEdit"]);
            //}

        }
        public int InsertUserInfo(UserInfo _objEntity)
        {
            string cmdText = @"INSERT INTO [LI_USERINFO]
           ([ACCOUNT],[UserName],[sex],[company] ,[address],[ZipCode],[Phone],[Email],[role],[CreateTime],[loginTime],[type],[Password])
                VALUES
           (@ACCOUNT,@UserName,@sex,@company,@address,@ZipCode,@Phone,@Email,@role,@CreateTime,@loginTime,@type,@Password)";
            SqlParameter[] parameters = new SqlParameter[]
                {
                    new SqlParameter("@ACCOUNT",SqlDbType.VarChar),
                    new SqlParameter("@UserName",SqlDbType.NVarChar),
                    new SqlParameter("@sex",SqlDbType.Bit),
                    new SqlParameter("@company",SqlDbType.NVarChar),
                    new SqlParameter("@address",SqlDbType.NVarChar),
                    new SqlParameter("@ZipCode",SqlDbType.VarChar),
                    new SqlParameter("@Phone",SqlDbType.VarChar),
                    new SqlParameter("@Email",SqlDbType.VarChar),
                    new SqlParameter("@role",SqlDbType.Int),
                    new SqlParameter("@CreateTime",SqlDbType.DateTime),
                    new SqlParameter("@loginTime",SqlDbType.DateTime),
                    new SqlParameter("@type",SqlDbType.Bit),
                    new SqlParameter("@Password",SqlDbType.VarChar)
                };
            parameters[0].Value = _objEntity.Account;
            parameters[1].Value = _objEntity.UserName;
            parameters[2].Value = _objEntity.Sex;
            parameters[3].Value = _objEntity.Company;
            parameters[4].Value = _objEntity.Address;
            parameters[5].Value = _objEntity.ZipCode;
            parameters[6].Value = _objEntity.Phone;
            parameters[7].Value = _objEntity.Email;
            parameters[8].Value = _objEntity.Role;
            parameters[9].Value = _objEntity.CreateTime;
            parameters[10].Value = _objEntity.LoginTime;
            parameters[11].Value = _objEntity.Type;
            parameters[12].Value = _objEntity.Password;
            int excInt = DbHelperSQL.ExecuteSql(cmdText, parameters);
            return excInt;
        }
        public int UpdateUserInfo(UserInfo _objEntity)
        {
            string cmdText = @"UPDATE [LI_USERINFO]
               SET [UserName] = @UserName
                  ,[sex] = @sex
                  ,[company] = @company
                  ,[address] = @address
                  ,[ZipCode] = @ZipCode
                  ,[Phone] = @Phone
                  ,[Email] = @Email
                  ,[role] = @role
                  ,[CreateTime] = @CreateTime
                  ,[loginTime] = @loginTime
             WHERE [ACCOUNT] = @ACCOUNT";
            SqlParameter[] parameters = new SqlParameter[]
                {
                    new SqlParameter("@ACCOUNT",SqlDbType.VarChar),
                    new SqlParameter("@UserName",SqlDbType.NVarChar),
                    new SqlParameter("@sex",SqlDbType.Bit),
                    new SqlParameter("@company",SqlDbType.NVarChar),
                    new SqlParameter("@address",SqlDbType.NVarChar),
                    new SqlParameter("@ZipCode",SqlDbType.VarChar),
                    new SqlParameter("@Phone",SqlDbType.VarChar),
                    new SqlParameter("@Email",SqlDbType.VarChar),
                    new SqlParameter("@role",SqlDbType.Int),
                    new SqlParameter("@CreateTime",SqlDbType.DateTime),
                    new SqlParameter("@loginTime",SqlDbType.DateTime)
                };
            parameters[0].Value = _objEntity.Account;
            parameters[1].Value = _objEntity.UserName;
            parameters[2].Value = _objEntity.Sex;
            parameters[3].Value = _objEntity.Company;
            parameters[4].Value = _objEntity.Address;
            parameters[5].Value = _objEntity.ZipCode;
            parameters[6].Value = _objEntity.Phone;
            parameters[7].Value = _objEntity.Email;
            parameters[8].Value = _objEntity.Role;
            parameters[9].Value = _objEntity.CreateTime;
            parameters[10].Value = _objEntity.LoginTime;
            int excInt = DbHelperSQL.ExecuteSql(cmdText, parameters);
            return excInt;
        }
        public int UpdateUserInfoType(string _Account,int _Type)
        {
            string cmdText = @"UPDATE [LI_USERINFO] SET [type] = @type WHERE [ACCOUNT] = @ACCOUNT";
            SqlParameter[] parameters = new SqlParameter[]
                {
                    new SqlParameter("@ACCOUNT",SqlDbType.VarChar),
                    new SqlParameter("@type",SqlDbType.Bit)
                };
            parameters[0].Value = _Account;
            parameters[1].Value = _Type;
            int excInt = DbHelperSQL.ExecuteSql(cmdText, parameters);
            return excInt;
        }
        public int DelUserInfoType(string _Account)
        {
            string cmdText = @"delete from [LI_USERINFO] WHERE [ACCOUNT] = @ACCOUNT";
            SqlParameter[] parameters = new SqlParameter[]
                {
                    new SqlParameter("@ACCOUNT",SqlDbType.VarChar)
                };
            parameters[0].Value = _Account;
            int excInt = DbHelperSQL.ExecuteSql(cmdText, parameters);
            return excInt;
        }
        public int UpdateUserInfoRole(string _Account, int _Role)
        {
            string cmdText = @"UPDATE [LI_USERINFO] SET [Role] = @Role WHERE [ACCOUNT] = @ACCOUNT";
            SqlParameter[] parameters = new SqlParameter[]
                {
                    new SqlParameter("@ACCOUNT",SqlDbType.VarChar),
                    new SqlParameter("@Role",SqlDbType.Int)
                };
            parameters[0].Value = _Account;
            parameters[1].Value = _Role;
            int excInt = DbHelperSQL.ExecuteSql(cmdText, parameters);
            return excInt;
        }
        public UserInfo GetOnlyUserInfo(string _Account)
        {
            UserInfo objUser = new UserInfo();
            string cmdText = @"select * from LI_USERINFO where ACCOUNT=@ACCOUNT";
            ArrayList objArryParam = new ArrayList();
            objArryParam.Add(_Account);
            List<UserInfo> objList = Select<UserInfo>(cmdText, objArryParam.ToArray());
            if (objList.Count > 0)
            {
                objUser = objList[0];
            }
            return objUser;
        }
        public List<UserInfo> GetAllUserInfo()
        {
            string cmdText = @"select * from LI_USERINFO";
            return Select<UserInfo>(cmdText);
        }


        public int GetAllUserInfoPageCount(ArrayList objArryParamName, ArrayList objArryParam)
        {
            StringBuilder strCountSql = new StringBuilder();
            strCountSql.Append(BuildSqlUserInfo(objArryParamName));

            StringBuilder sbCountSql = new StringBuilder();
            sbCountSql.AppendLine("SELECT COUNT(*) FROM (");
            sbCountSql.Append(strCountSql.ToString());
            sbCountSql.AppendLine(") TB");
            int retRecc = 0;
            using (DbCommand cmd = db.GetSqlStringCommand(sbCountSql.ToString()))
            {
                MatchCollection mc;
                mc = Regex.Matches(cmd.CommandText, @"(@[^ \(]+?)(\,| |$|\))");
                for (int i = 0; i < mc.Count; i++)
                {
                    AddInParameter(cmd, mc[i].Groups[1].Value, objArryParam[i]);
                }
                var result = db.ExecuteScalar(cmd);
                retRecc = null == result ? 0 : int.Parse(result.ToString());
            }
            return retRecc;
        }
        public List<UserInfo> GetAllUserInfoPage(ArrayList objArryParamName, ArrayList objArryParam, int StartIndex, int EndIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(BuildSqlUserInfo(objArryParamName));
            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendLine(@"SELECT ACCOUNT,USERNAME,SEX,COMPANY,ADDRESS,ZIPCODE,PHONE,EMAIL,ROLE,CREATETIME,LOGINTIME,TYPE,PASSWORD FROM (
				SELECT ACCOUNT,USERNAME,SEX,COMPANY,ADDRESS,ZIPCODE,PHONE,EMAIL,ROLE,CREATETIME,LOGINTIME,TYPE,PASSWORD,ROW_NUMBER() OVER(ORDER BY [CREATETIME] DESC) AS RowId FROM (");
            sbSql.Append(strSql.ToString());
            sbSql.AppendLine(") TB");
            sbSql.AppendLine(") TT");
            sbSql.AppendFormat(" WHERE RowId BETWEEN {0} AND {1}", StartIndex, EndIndex);
            List<UserInfo> objList = Select<UserInfo>(sbSql.ToString(), objArryParam.ToArray());
            return objList;
        }
        private StringBuilder BuildSqlUserInfo(ArrayList objArryParamName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from LI_USERINFO ");
            if (objArryParamName.Count > 0)
            {
                strSql.Append("where 1=1 and ");
                for (int i = 0; i < objArryParamName.Count; i++)
                {
                    if (i + 1 == objArryParamName.Count)
                    {
                        strSql.Append(objArryParamName[i].ToString()).Append("=@").Append(objArryParamName[i].ToString() + i.ToString()).Append(" ");
                    }
                    else
                    {
                        strSql.Append(objArryParamName[i].ToString()).Append("=@").Append(objArryParamName[i].ToString() + i.ToString()).Append(" and ");
                    }
                }
            }
            return strSql;
        }
    }
}
